class MySQLConnect: #MySQL connection class    
    def __init__(self):
    	self.year = raw_input('Enter last 2 digits of year (2011 = 11): ')
    	self.term = raw_input('Enter term code (A, W, S, U): ')
        self.cursor = None
        self.conn = self.connect()
        self.accumulator = self.getNewClassID()
        self.sectAccum = self.getNewSectionID()
        self.termAccum = self.getNewTermID()
        self.discs = self.getDisciplines()
        self.throttle = 0

    def connect(self): #connect method to establish connection to db
        try:
            conn = MySQLdb.connect (host = "whoisryannystrom.com",
                                    user = "apadget",
                                    passwd = "ucclasses",
                                    db = "ucclasses_dev")
        except MySQLdb.Error, e:
            raise
        return conn

    def getDisciplines(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("""SELECT abbr,college_collegeID,discID FROM discipline""")
        result_set = self.cursor.fetchall()
        return {(x,y):z for (x,y,z) in result_set}

	#returns true if the class exists, false otherwise
    def getCourse(self, item):
        self.cursor = self.conn.cursor()
        self.cursor.execute("""SELECT classID FROM classes WHERE abbrName = %s""", item.courseName)
        print "Searching for class match %s..." % item.courseName
        return self.cursor.fetchall() != None
        
    def getCourseTerm(self, item):
        self.cursor = self.conn.cursor()
        self.cursor.execute("""SELECT c.classID FROM classes AS c JOIN terms AS t ON t.classes_classID = c.classID WHERE c.abbrName = %s AND t.year = %s AND t.quarter = %s""",(item.courseName,self.year,self.term))
        print "Searching for term match %s..." % item.courseName
        return self.cursor.fetchall() != None

    def getNewClassID(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("""SELECT MAX(classID) + 1 FROM classes""")
        return self.cursor.fetchone()[0]
        
    def getNewSectionID(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("""SELECT MAX(sectionID) + 1 FROM sections""")
        return self.cursor.fetchone()[0]
        
    def getNewTermID(self):
        self.cursor = self.conn.cursor()
        self.cursor.execute("""SELECT MAX(t_ID) + 1 FROM terms""")
        return self.cursor.fetchone()[0]

    def insertCourse(self,item):        
        if self.throttle == 100:
            import time
            print 'throttling...'
            time.sleep(10)
            self.throttle = 0
        else:
            self.throttle += 1
        
        #if class exists but not for the selected year/term
        if self.getCourse(item):         
            print "NEW TERM %s... " % item.courseName
            self.cursor = self.conn.cursor()
            self.cursor.execute("""UPDATE classes SET courseNumber = %s, discipline_discID = %s WHERE classID = %s""",(item.courseNum,self.discs[item.discipline,item.college],item.courseName))
            self.cursor.execute("""UPDATE classes_info AS ci JOIN classes AS c ON ci.classes_classID = c.classID SET ci.longName = %s, ci.classDescrip = %s, ci.bok = %s, ci.numCredits = %s, ci.isHonorsClass = %s WHERE c.abbrName = %s AND c.courseNumber = %s""",(item.longName,item.desc,item.bok,item.credits,item.isHonors,item.courseName,item.courseNum))     
            self.cursor.execute("""INSERT INTO terms (t_ID,classes_classID,year,quarter) VALUES (NULL,%s,%s,%s)""",(self.accumulator,self.year,self.term))
            for sect in item.sections:
                self.cursor.execute("""INSERT INTO sections (classID,sectionNumber,callNumber,flexDates,instructor,location,termID) VALUES (%s,%s,%s,%s,%s,%s,%s)""",(self.accumulator,sect.sectionNum,sect.callNum,item.flexDates,sect.instructor,sect.location,self.termAccum))
                self.conn.commit()
                for day in sect.schedule:
                    self.cursor.execute("""INSERT INTO days (sectionID,classID,days,startTime,endTime) VALUES (%s,%s,%s,%s,%s)""",(self.sectAccum,self.accumulator,day['days'],day['startTime'],day['endTime']))
                self.conn.commit()
                self.sectAccum += 1
            self.accumulator += 1
            self.termAccum += 1   
        #add a new class
        else:
            print "INSERTING %s..." % item.courseName
            self.cursor = self.conn.cursor()
            self.cursor.execute("""INSERT INTO classes (classID,abbrName,courseNumber,discipline_discID) VALUES (NULL,%s,%s,%s)""",(item.courseName,item.courseNum,self.discs[item.discipline,item.college]))
            self.conn.commit()
            self.cursor.execute("""INSERT INTO classes_info (classes_classID,longName,classDescrip,bok,numCredits,isHonorsClass) VALUES (%s,%s,%s,%s,%s,BINARY %s = '1')""",(self.accumulator,item.longName,item.desc,item.bok,item.credits,item.isHonors))
            self.cursor.execute("""INSERT INTO terms (t_ID, classes_classID, year, quarter) VALUES (NULL,%s,%s,%s)""",(self.accumulator, self.year, self.term))
            for sect in item.sections:
                self.cursor.execute("""INSERT INTO sections (classID,sectionNumber,callNumber,flexDates,instructor,location,termID) VALUES (%s,%s,%s,%s,%s,%s,%s)""",(self.accumulator,sect.sectionNum,sect.callNum,item.flexDates,sect.instructor,sect.location,self.termAccum))
                self.conn.commit()
                for day in sect.schedule:
                    self.cursor.execute("""INSERT INTO days (sectionID,classID,days,startTime,endTime) VALUES (%s,%s,%s,%s,%s)""",(self.sectAccum,self.accumulator,day['days'],day['startTime'],day['endTime']))
                self.conn.commit()
                self.sectAccum += 1
            self.accumulator += 1
            self.termAccum += 1

    def updateCourse(self,item):        
        if self.throttle == 100:
            import time
            print 'throttling...'
            time.sleep(10)
            self.throttle = 0
        else:
            self.throttle += 1
        
        print "UPDATING %s... " % item.courseName
        self.cursor = self.conn.cursor()
        self.cursor.execute("""UPDATE classes SET courseNumber = %s, discipline_discID = %s WHERE classID = %s""",(item.courseNum,self.discs[item.discipline,item.college],item.courseName))
        self.conn.commit()
        self.cursor.execute("""UPDATE classes_info AS ci JOIN classes AS c ON ci.classes_classID = c.classID SET ci.longName = %s, ci.classDescrip = %s, ci.bok = %s, ci.numCredits = %s, ci.isHonorsClass = %s WHERE c.abbrName = %s AND c.courseNumber = %s""",(item.longName,item.desc,item.bok,item.credits,item.isHonors,item.courseName,item.courseNum))        

        for sect in item.sections:
            self.cursor.execute("""UPDATE sections AS s JOIN classes AS c ON s.classID = c.classID SET s.callNumber = %s, s.flexDates = %s, s.instructor = %s, s.location = %s WHERE c.abbrName = %s AND c.courseNumber = %s AND s.sectionNumber = %s""",(sect.callNum,item.flexDates,sect.instructor,sect.location,item.courseName,item.courseNum,sect.sectionNum))
            self.conn.commit()
        
import json,MySQLdb,sys
from ucCourse import *
while(1):
    f = raw_input('Name of file to run: ')
    try:
        data = open(f).read()
        content = json.loads(data)
        courses = [x for x in content['courses']]
        myCourses = [Course(x) for x in courses]
        SQLcon = MySQLConnect()
        for item in myCourses:        
            if SQLcon.getCourseTerm(item):
                SQLcon.insertCourse(item)
            else:
                SQLcon.updateCourse(item)
        exit = raw_input('Run another file [y/n]? ')
        if(exit == 'n' or exit == 'N' or exit == 'no' or exit == 'No'):
            sys.exit(1)        
    except IOError:
        print "File not found"
